package com.rybbaby.tss.platform.codeGenerate.dao.imp;

import com.rybbaby.tss.core.bean.ForeignKeyBean;
import com.rybbaby.tss.core.bean.TableBean;
import com.rybbaby.tss.core.bean.TableColumnBean;
import com.rybbaby.tss.core.bean.UploadFileConfig;
import com.rybbaby.tss.core.code.generate.config.GenerateConfig;
import com.rybbaby.tss.core.sql.metadata.DatabaseMetadata;
import com.rybbaby.tss.core.sql.metadata.ForeignKeyMetadata;
import com.rybbaby.tss.core.sql.metadata.TableMetadata;
import com.rybbaby.tss.core.utils.CodeGenerateUtil;
import com.rybbaby.tss.core.utils.PropertiesReadUtil;
import com.rybbaby.tss.core.web.dao.imp.GenericDao;
import com.rybbaby.tss.platform.codeGenerate.dao.ICodeGenerateDao;
import com.rybbaby.tss.platform.sysTableColumn.dao.ISysTableColumnMappingDao;
import com.rybbaby.tss.platform.sysTableColumn.entity.SysTableColumnMapping;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.apache.commons.lang.BooleanUtils;
import org.apache.commons.lang.StringUtils;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.hibernate.dialect.Dialect;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

/**
 * @description 代码生成数据库操作接口实现
 *
 * @author 谢进伟
 *
 * @createDate 2016年2月14日 下午4:42:44
 */
@Repository
public class CodeGenerateDaoImp extends GenericDao<TableColumnBean> implements ICodeGenerateDao {
	
	@Autowired
	private HttpServletRequest request;
	@Autowired
	private ISysTableColumnMappingDao sysTableColumnMappingDao;
	
	public void setRequest(HttpServletRequest request) {
		this.request = request;
	}
	
	@Override
	public List<String> getAllCatalog() throws ClassNotFoundException , InstantiationException , IllegalAccessException , SQLException {
		List<String> allCatalogs = new ArrayList<String>();
		Connection conn = null;
		ResultSet catalogsResultSet = null;
		ResultSet schemasResultSet = null;
		try {
			conn = this.getConnection(false);
			DatabaseMetaData connMetaData = conn.getMetaData();
			catalogsResultSet = connMetaData.getCatalogs();
			readResultSet(allCatalogs , catalogsResultSet);
			schemasResultSet = connMetaData.getSchemas();
			readResultSet(allCatalogs , schemasResultSet);
		} catch (Exception e) {
			throw e;
		} finally {
			this.closeResouces(conn , null , catalogsResultSet);
			this.closeResouces(conn , null , schemasResultSet);
		}
		return allCatalogs;
	}
	
	@Override
	public List<TableBean> getAllTable(String catalog) throws ClassNotFoundException , InstantiationException , IllegalAccessException , SQLException {
		List<TableBean> allTables = new ArrayList<TableBean>();
		Connection connection = null;
		ResultSet tablesResultSet = null;
		try {
			connection = this.getConnection(true);
			DatabaseMetaData connMetaData = connection.getMetaData();
			tablesResultSet = connMetaData.getTables(catalog , null , null , new String []{"TABLE" , "VIEW"});
			while (tablesResultSet.next()) {
				String cat = tablesResultSet.getString("TABLE_CAT");
				String schema = tablesResultSet.getString("TABLE_SCHEM");
				String name = tablesResultSet.getString("TABLE_NAME");
				String remarks = tablesResultSet.getString("REMARKS");
				TableBean tb = new TableBean(schema , cat , name , catalog , remarks);
				allTables.add(tb);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			this.closeResouces(connection , null , tablesResultSet);
		}
		return allTables;
	}
	
	@Override
	public String getTableRemarks(String catalog , String tableName) throws Exception {
		String remarks = "";
		Connection connection = null;
		ResultSet tablesResultSet = null;
		try {
			connection = this.getConnection(true);
			DatabaseMetaData connMetaData = connection.getMetaData();
			tablesResultSet = connMetaData.getTables(catalog , null , tableName , new String []{"TABLE" , "VIEW"});
			if(tablesResultSet.next()) {
				remarks = tablesResultSet.getString("REMARKS");
			}
		} catch (Exception e) {
			throw e;
		} finally {
			this.closeResouces(connection , null , tablesResultSet);
		}
		return remarks;
	}
	
	@Override
	public void autoGenCode(GenerateConfig gc) throws ClassNotFoundException , InstantiationException , IllegalAccessException , SQLException {
		Set<String> tables = gc.getGenFielsMaps().keySet();
		String catalog = gc.getCatalog();// TODO 跨库关联的问题暂时不考虑
		CodeGenerateUtil gu = new CodeGenerateUtil(gc);
		Map<String , String> businessDescriptionMaps = gc.getBusinessDescriptionMaps();
		List<SysTableColumnMapping> columnMapping = null;
		List<TableColumnBean> columns = null;
		List<ForeignKeyBean> refTables = null;
		List<TableColumnBean> tempList = null;
		TableColumnBean tcb = null;
		ForeignKeyBean fkb = null;
		for(String tableName : tables) {
			columns = new ArrayList<TableColumnBean>();
			refTables = new ArrayList<ForeignKeyBean>();
			DetachedCriteria dc = DetachedCriteria.forClass(SysTableColumnMapping.class);
			dc.add(Restrictions.eqOrIsNull("scheme" , catalog));
			dc.add(Restrictions.eqOrIsNull("tableName" , tableName));
			columnMapping = this.sysTableColumnMappingDao.findByDetached(dc);
			for(SysTableColumnMapping sysTableColumnMapping : columnMapping) {
				tcb = new TableColumnBean();
				// 复制映射信息
				BeanUtils.copyProperties(sysTableColumnMapping , tcb);
				// 解析文件上传组件配置参数
				String uploadConfigJsonStr = sysTableColumnMapping.getUploadConfig();
				if(StringUtils.isNotBlank(uploadConfigJsonStr)) {
					UploadFileConfig ucf = new UploadFileConfig();
					Class<? extends UploadFileConfig> cls = ucf.getClass();
					JSONArray jsonArray = JSONArray.fromObject(uploadConfigJsonStr);
					for(int i = 0 , len = jsonArray.size() ; i < len ; i++) {
						JSONObject jsonObject = jsonArray.getJSONObject(i);
						String name = jsonObject.getString("name");
						String value = jsonObject.getString("value");
						try {
							Field field = cls.getDeclaredField(name);
							if(field != null) {
								field.setAccessible(true);
								field.set(ucf , value);
							}
						} catch (NoSuchFieldException | SecurityException e) {
						}
					}
					tcb.setUcf(ucf);
				}
				// TODO 设置行内样式、属性、事件
				// 字段HTML属性设置
				Map<String , Map<String , Object>> attrJsonConfig = gc.getAttrJsonConfig(tableName);
				if(attrJsonConfig != null) {
					tcb.setAttrs(attrJsonConfig.get(sysTableColumnMapping.getColumnJavaFieldName()));
				}
				// 添加到生成列集合
				columns.add(tcb);
				// 设置外键信息
				if(tcb.getIsForeignKey() != null && tcb.getIsForeignKey()) {
					String referenceTable = tcb.getReferenceTable();
					fkb = new ForeignKeyBean(tcb.getForeignKeyName() , referenceTable , tcb.getColumnName() , tcb.getReferenceColumn() , tcb.getRemarks());
					refTables.add(fkb);
					String referenceTableShowColumns = sysTableColumnMapping.getReferenceTableShowColumns();
					String referenceTableSearchColumns = sysTableColumnMapping.getReferenceTableSearchColumns();
					if(StringUtils.isNotBlank(referenceTableShowColumns)) {
						tempList = getRealJavaFields(catalog , referenceTable , referenceTableShowColumns);
						tcb.setReferenceTableShowColumns(tempList);
					}
					if(StringUtils.isNotBlank(referenceTableSearchColumns)) {
						tempList = getRealJavaFields(catalog , referenceTable , referenceTableSearchColumns);
						tcb.setReferenceTableSearchColumns(tempList);
					}
				}
			}
			if(businessDescriptionMaps != null && !businessDescriptionMaps.isEmpty()) {
				gc.setBusinessDescription(businessDescriptionMaps.get(tableName));
			} else {
				gc.setBusinessDescription(tableName);
			}
			gc.setForeignKeys(refTables);
			gc.setTableName(tableName);
			gc.setTableName(tableName);
			gc.setEntityName(com.rybbaby.tss.core.utils.StringUtils.converToJavaVariable(tableName , false));
			gu.generateCode(columns);
		}
	}
	
	@Override
	public List<TableColumnBean> getTableColumns(String catalog , String tableName) throws ClassNotFoundException , SQLException , InstantiationException , IllegalAccessException {
		List<TableColumnBean> list = new ArrayList<TableColumnBean>();
		if(StringUtils.isNotBlank(tableName)) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				conn = this.getConnection(false);
				Class<?> dialectCls = Class.forName(PropertiesReadUtil.getStringProperty("hibernate.dialect"));
				Dialect dialect = (Dialect)dialectCls.newInstance();
				DatabaseMetadata dm = new DatabaseMetadata(conn , dialect);
				String schema = null;
				boolean isQuoted = false;
				Map<String , ForeignKeyBean> foreignKeyMap = new HashMap<String , ForeignKeyBean>();
				List<String> primaryKeysStr = null;
				TableMetadata tableMetadata = dm.getTableMetadata(tableName , schema , catalog , isQuoted);
				if(tableMetadata != null) {
					primaryKeysStr = tableMetadata.getPrimaryKeysStr();
					Map<String , ForeignKeyMetadata> foreignKeys = tableMetadata.getForeignKeys();
					Iterator<String> iterator = foreignKeys.keySet().iterator();
					while (iterator.hasNext()) {
						String key = iterator.next();
						ForeignKeyMetadata foreignKeyMetadata = foreignKeys.get(key);
						String foreignKeyName = foreignKeyMetadata.getName();
						String referenceTableName = foreignKeyMetadata.getReferencedTableName();
						Map<String , String> references = foreignKeyMetadata.getReferences();
						Iterator<String> itera = references.keySet().iterator();
						while (itera.hasNext()) {
							String referenceColumnName = itera.next();
							String foreignKeyColumnName = references.get(referenceColumnName);
							ForeignKeyBean fkb = new ForeignKeyBean(foreignKeyName , referenceTableName , referenceColumnName , foreignKeyColumnName , "");
							foreignKeyMap.put(referenceColumnName , fkb);
						}
					}
				}
				String sql = "SELECT * FROM " + catalog + "." + tableName;
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				ResultSetMetaData metaData = rs.getMetaData();
				int columnCount = metaData.getColumnCount();
				for(int column = 1 ; column <= columnCount ; column++) {
					String catalogName = metaData.getCatalogName(column);
					String columnClassName = metaData.getColumnClassName(column);
					// String columnLabel = metaData.getColumnLabel(column);
					String columnName = metaData.getColumnName(column);
					String columnTypeName = metaData.getColumnTypeName(column);
					// String schemaName = metaData.getSchemaName(column);
					String dbTableName = metaData.getTableName(column);
					int columnDisplaySize = metaData.getColumnDisplaySize(column);
					int columnType = metaData.getColumnType(column);
					int precision = metaData.getPrecision(column);
					int scale = metaData.getScale(column);
					int nullable = metaData.isNullable(column);
					// boolean currency = metaData.isCurrency(column);
					boolean signed = metaData.isSigned(column);
					boolean autoIncrement = metaData.isAutoIncrement(column);
					boolean caseSensitive = metaData.isCaseSensitive(column);
					boolean readOnly = metaData.isReadOnly(column);
					boolean writable = metaData.isWritable(column);
					boolean searchable = metaData.isSearchable(column);
					TableColumnBean tc = new TableColumnBean();
					tc.setCatalogName(catalogName);
					tc.setColumnClassName(columnClassName);
					tc.setColumnDisplaySize(columnDisplaySize);
					tc.setColumnName(columnName);
					tc.setColumnType(columnType);
					tc.setColumnTypeName(columnTypeName);
					tc.setIsAutoIncrement(autoIncrement);
					tc.setIsCaseSensitive(caseSensitive);
					tc.setIsNullable(BooleanUtils.toBooleanObject(nullable));
					tc.setIsReadOnly(readOnly);
					tc.setIsSearchable(searchable);
					tc.setIsSigned(signed);
					tc.setIsWritable(writable);
					tc.setPrecision(precision);
					tc.setScale(scale);
					tc.setTableName(dbTableName);
					String lowerCaseColumnName = tc.getColumnName().toLowerCase();
					tc.setPrimaryKey(primaryKeysStr != null && primaryKeysStr.contains(lowerCaseColumnName));
					if(foreignKeyMap.containsKey(lowerCaseColumnName)) {
						ForeignKeyBean foreignKeyBean = foreignKeyMap.get(lowerCaseColumnName);
						tc.setForeignKey(true);
						tc.setForeignKeyName(foreignKeyBean.getForeignKeyName());
						tc.setReferenceColumn(foreignKeyBean.getForeignKeyColumnName());
						tc.setReferenceTable(foreignKeyBean.getReferenceTableName());
					}
					tc.setRemarks(tableMetadata.getColumnMetadata(columnName).getRemarks());
					list.add(tc);
				}
			} catch (Exception e) {
				throw e;
			} finally {
				this.closeResouces(conn , ps , rs);
			}
		}
		return list;
	}
	
	private List<TableColumnBean> getRealJavaFields(String catalog , String referenceTable , String referenceTableSearchColumns) {
		List<SysTableColumnMapping> tempMappingList;
		List<TableColumnBean> tempList;
		DetachedCriteria tempDc;
		tempList = new ArrayList<TableColumnBean>();
		for(String referenceTableShowColumn : referenceTableSearchColumns.split(",")) {
			tempDc = DetachedCriteria.forClass(SysTableColumnMapping.class);
			tempDc.add(Restrictions.eqOrIsNull("scheme" , catalog));
			tempDc.add(Restrictions.eqOrIsNull("tableName" , referenceTable));
			tempDc.add(Restrictions.eq("columnName" , referenceTableShowColumn));
			tempMappingList = this.sysTableColumnMappingDao.findByDetached(tempDc);
			if(tempMappingList != null && !tempMappingList.isEmpty()) {
				SysTableColumnMapping newReferenceTableShowColumn = tempMappingList.get(0);
				TableColumnBean tcb = new TableColumnBean();
				BeanUtils.copyProperties(newReferenceTableShowColumn , tcb);
				tempList.add(tcb);
			}
		}
		return tempList;
	}
	
	/**
	 * 获取连接
	 * 
	 * @param isLoadRemarks
	 *            是否加载表备注
	 * @return
	 * @throws SQLException
	 * @throws ClassNotFoundException
	 */
	private Connection getConnection(boolean isLoadRemarks) throws SQLException , ClassNotFoundException {
		GenerateConfig gc = null;
		if(request != null) {
			HttpSession session = request.getSession();
			if(session != null) {
				Object customConfig = session.getAttribute("generateCustomConfig");
				if(customConfig instanceof GenerateConfig) {
					gc = (GenerateConfig)customConfig;
				}
			}
			if(gc == null) {
				gc = new GenerateConfig();
			}
			Class.forName(gc.getJdbcDriver());
			Properties props = new Properties();
			if(isLoadRemarks) {
				// 设置可以获取remarks信息
				props.setProperty("remarks" , "true");
				props.setProperty("useInformationSchema" , "true");
			}
			// 基本连接参数
			props.put("user" , gc.getJdbcUserName());
			props.put("password" , gc.getJdbcPassword());
			return DriverManager.getConnection(gc.getJdbcUrl() , props);
		} else {
			return null;
		}
	}
	
	/**
	 * 关闭相关数据操作资源
	 * 
	 * @param conn
	 *            连接对象
	 * @param ps
	 *            操作对象
	 * @param rs
	 *            查询结果集
	 */
	private void closeResouces(Connection conn , PreparedStatement ps , ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 读取ResultSet中的值
	 * 
	 * @param list
	 *            存放容器
	 * @param resultSet
	 *            数据源
	 * @throws SQLException
	 */
	private void readResultSet(List<String> list , ResultSet resultSet) throws SQLException {
		while (resultSet.next()) {
			Object object = resultSet.getObject(1);
			if(!list.contains(object.toString())) {
				list.add(object.toString());
			}
		}
	}
}
